﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using System.Data.SqlClient;


namespace ClassDAL
{

    public class t_class_user_fileupalod_DAL
    {
        /// <summary>
        /// 插入用户上传文件数据
        /// </summary>
        /// <returns>返回自增ID</returns>
        public static int InsertUpload(t_class_fileupload fileupload)
        {
            if (fileupload == null) throw new ArgumentException("上传文件model为空了!");

            StringBuilder insertBuilder = new StringBuilder();

            // 一个排课任务只对于一个Excel.
            // 先要把之前的Excel删除
            insertBuilder.AppendLine("DELETE FROM t_user_uploadFile WHERE taskid = @taskId and userid = @userid;");
            insertBuilder.AppendLine("INSERT INTO t_user_uploadFile (userid,originName,newName,fileSize,extensionName,savePath,contentType,uploadDate,taskId) ");
            insertBuilder.AppendLine(" VALUES(");
            insertBuilder.AppendLine("@userid,@originName,@newName,@fileSize,@extensionName,@savePath,@contentType,@uploadDate,@taskId);");
            insertBuilder.AppendLine("SELECT @@IDENTITY;");

            SqlParameter[] insertParams = new SqlParameter[] 
            {
                new SqlParameter("@userid",fileupload.userId),
                new SqlParameter("@originName",fileupload.orginName),
                new SqlParameter("@newName",fileupload.newName),
                new SqlParameter("@fileSize",fileupload.fileSize),
                new SqlParameter("@extensionName",fileupload.extensionName),
                new SqlParameter("@savePath",fileupload.savePath),
                new SqlParameter("@contentType",fileupload.contentType),
                new SqlParameter("@uploadDate",DateTime.Now),
                new SqlParameter("@taskId",fileupload.TaskId)
            };


            int increaseID = Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString(), insertParams));

            return increaseID;
        }

        /// <summary>
        /// 根据任务ID获取用户上传的excel表格。
        /// </summary>
        /// <param name="taskId"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static t_class_fileupload GetExcelFileWithTaskId(int taskId, int userid)
        {
            string getSQL = "select id,userid,originName,newName,fileSize,extensionName,savePath,uploadDate,taskid,contentType from t_user_uploadFile where taskid = @taskid and userid = @userid;";

            SqlParameter[] getParams = new SqlParameter[] 
            {
                new SqlParameter("@taskid",taskId),
                new SqlParameter("@userid",userid)
            };

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), System.Data.CommandType.Text, getSQL, getParams);
            if (!reader.Read()) return null;

            t_class_fileupload fileupload = new t_class_fileupload();
            fileupload.id = Convert.ToInt32(reader["id"]);
            fileupload.userId = Convert.ToInt32(reader["userid"]);
            fileupload.orginName = reader["originName"].ToString();
            fileupload.newName = reader["newName"].ToString();
            fileupload.fileSize = Convert.ToInt32(reader["fileSize"]);
            fileupload.extensionName = reader["extensionName"].ToString();
            fileupload.savePath = reader["savePath"].ToString();
            fileupload.uploadDate = Convert.ToDateTime(reader["uploadDate"]);
            fileupload.TaskId = Convert.ToInt32(reader["taskid"]);
            fileupload.contentType = reader["contentType"].ToString();

            reader.Close();

            return fileupload;
        }



        /// <summary>
        /// 这个文件都是当前登录用户的吗？
        /// </summary>
        /// <param name="fileid">文件id</param>
        /// <param name="userid">用户id</param>
        /// <returns>yes 是  no 不是</returns>
        public static bool theFileIsCurrentLoginerWith(int fileid, int userid)
        {
            string checkSQL = "select COUNT(1) from t_user_uploadFile where taskid = @fileid and userid = @userid;";
            SqlParameter[] checkParams = new SqlParameter[] 
            {
                new SqlParameter("@fileid",fileid),
                new SqlParameter("@userid",userid)
            };

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, checkSQL, checkParams)) > 0;
        }


        /// <summary>
        /// 根据文件id获取上传文件model
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public static t_class_fileupload getSingleUploadFile(int fileId)
        {
            string getSQL = "select id,userid,originName,newName,fileSize,extensionName,savePath,uploadDate,taskId,contentType from t_user_uploadFile where id = @id;";
            SqlParameter getParam = new SqlParameter("@id", fileId);

            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), System.Data.CommandType.Text, getSQL, getParam))
            {
                if (!reader.Read()) return null;

                t_class_fileupload upload = new t_class_fileupload();
                upload.id = Convert.ToInt32(reader["id"]);
                upload.userId = Convert.ToInt32(reader["userid"]);
                upload.orginName = reader["originName"].ToString();
                upload.fileSize = Convert.ToInt32(reader["fileSize"]);
                upload.extensionName = reader["extensionName"].ToString();
                upload.savePath = reader["savePath"].ToString();
                upload.uploadDate = Convert.ToDateTime(reader["uploadDate"]);
                upload.TaskId = Convert.ToInt32(reader["taskId"]);
                upload.contentType = reader["contentType"].ToString();

                return upload;
            }
        }

        /// <summary>
        /// 根据taskid 获取对应的上传文件id
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public static int getFileIdWithTaskId(int taskId)
        {
            string getSQL = "select id from t_user_uploadFile where taskId = @taskid;";
            SqlParameter getParam = new SqlParameter("@taskid", taskId);

            object obj = SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, getSQL, getParam);
            return obj == DBNull.Value ? -1 : Convert.ToInt32(obj);
        }


    }
}
